using System;
using System.Text;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using System.Security.Cryptography;

namespace TestProject
{
	/// <summary>
	/// Class for a user. 
	/// </summary>
	public class User
	{
		private int m_user_ID = 0;
		private string m_username;
		private string m_password;
		private string m_email;
		private string m_firstname;
		private string m_lastname;
		private string m_contactnumber;
		private int m_level;
		private bool m_isFirstTime;
		
		public User()
		{
		}
		
		public User(string username, string password)
		{
	        IDbConnection dbcon;
	        dbcon = new MySqlConnection( ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        dbcon.Open();
	        IDbCommand dbcmd = dbcon.CreateCommand();
			string statement = "SELECT user_id "
				+ "FROM users WHERE username = '" + username + "' and password = '" + encrypt(password) + "'";			
	        dbcmd.CommandText = statement;
	        IDataReader reader = dbcmd.ExecuteReader();
	        while(reader.Read()) {
				m_user_ID = (int) reader["user_id"];
	        }
	        reader.Close();
	        reader = null;
	        dbcmd.Dispose();
	        dbcmd = null;
	        dbcon.Close();
	        dbcon = null;
			getData();
		}
		
		public User(int user_ID)
		{
			if (user_ID!=0)
			{
				this.m_user_ID = user_ID;
				getData();
			}
		}
		
		public int user_ID
		{
			set {
				m_user_ID = value;
				getData();
			}
			get {
				return m_user_ID;
			}
		}
		
		public string username
		{
			set {
				m_username = value;
			}
			get {
				return m_username;
			}
		}
		
		public string firstname
		{
			set {
				m_firstname = value;
			}
			get {
				return m_firstname;
			}
		}
		
		public string lastname
		{
			set {
				m_lastname = value;
			}
			get {
				return m_lastname;
			}
		}
		
		public string password
		{
			set {
				m_password = value;
			}
			get {
				return m_password;
			}
		}
		
		public string email
		{
			set {
				m_email = value;
			}
			get {
				return m_email;
			}
		}
		
		public string contactnumber
		{
			set {
				m_contactnumber = value;
			}
			get {
				return m_contactnumber;
			}
		}
		
		public int level
		{
			set
			{
				m_level = value;
			}
			get
			{
				return m_level;
			}
		}
		
		public bool isFirstTime
		{
			set
			{
				if (value)
					Firsttime.addFirstTime(m_user_ID);
				else
					Firsttime.deleteFirstTime(m_user_ID);
				m_isFirstTime = value;
			}
			get
			{
				return m_isFirstTime;
			}
		}
		
		private void getData()
		{
	        MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT username, password, firstname, lastname, email, contactnumber, level "
					+ "FROM users WHERE user_id = '" + this.m_user_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					m_username = (string) reader["username"];
					m_password = (string) reader["password"];
					m_firstname = (string) reader["firstname"];
					m_lastname = (string) reader["lastname"];
					m_email = (string) reader["email"];
					m_contactnumber = (string) reader["contactnumber"];
					m_level = (int) reader["level"];
		        }
				reader.Close();
				string newStatement = "SELECT user_ID FROM firsttime WHERE user_ID='"+m_user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);			
		        MySqlDataReader newReader = newCmd.ExecuteReader();
				if (newReader.HasRows)
					m_isFirstTime = true;
				else
					m_isFirstTime = false;
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		public List<Reservation> getReservations()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			List<Reservation> toReturn = new List<Reservation>(0);
	        try
			{
				conn.Open();
				string statement = "SELECT reservation_ID "
					+ "FROM reservation WHERE user_ID = '" + m_user_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					toReturn.Add(new Reservation((int) reader["reservation_ID"]));
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;
		}
		
		public List<Loan> getLoans()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			List<Loan> toReturn = new List<Loan>(0);
	        try
			{
				conn.Open();
				string statement = "SELECT loan_ID, datereturned "
					+ "FROM loan WHERE user_ID = '" + m_user_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);		
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					if (reader.IsDBNull(1))
						toReturn.Add(new Loan((int) reader["loan_ID"]));
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();	
			return toReturn;
		}
		
		public List<Loan> getReturned()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			List<Loan> toReturn = new List<Loan>(0);
	        try
			{
				conn.Open();
				string statement = "SELECT loan_ID, datereturned "
					+ "FROM loan WHERE user_ID = '" + m_user_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					if (!reader.IsDBNull(1))
						toReturn.Add(new Loan((int) reader["loan_ID"]));
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;
		}
		
		/// <summary>
		/// Adds a new user to the database. The username of the new user must be unique from those
		/// store already or the user will not be added.
		/// </summary>
		/// <param name="username">
		/// A <see cref="System.String"/> The username of the user.
		/// </param>
		/// <param name="password">
		/// A <see cref="System.String"/> The password of the user.
		/// </param>
		/// <param name="firstname">
		/// A <see cref="System.String"/> The firstname of the user.
		/// </param>
		/// <param name="lastname">
		/// A <see cref="System.String"/> The lastname of the user.
		/// </param>
		/// <param name="email">
		/// A <see cref="System.String"/> The email of the user.
		/// </param>
		/// <param name="contact">
		/// A <see cref="System.String"/> The contact number of the user.
		/// </param>
		/// <param name="level">
		/// A <see cref="System.Int32"/> The level of the user.
		/// </param>
		/// <returns>
		/// A <see cref="TestProject.User"/> Returns a User object with the passed information.
		/// </returns>
		public static User addUser(string username, string password, string firstname, string lastname, string email, string contact, int level)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			User toReturn = null;
	        try
			{
				conn.Open();
				string statement = "SELECT * "
					+ "FROM users WHERE username = '" + username + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
				if (reader.HasRows)
				{			
					conn.Close();
					reader.Close();
					return toReturn;	
				}
				reader.Close();
				string newStatement = "INSERT INTO users (username, password, firstname, lastname, email, contactnumber, level) "
					+ "VALUES ('"+username+"','"+encrypt(password)+"','"+firstname+"','"+lastname+"','"+email+"','"+contact+"','"+level+"')";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
				
				toReturn = new User();
				string userStatement = "SELECT user_ID FROM users WHERE username = '"+username+"' ORDER BY user_ID ASC";
				MySqlCommand userCmd = new MySqlCommand(userStatement, conn);
				MySqlDataReader userReader = userCmd.ExecuteReader();
				while(userReader.Read())
				{
					toReturn.user_ID = (int)userReader["user_ID"];
				}
				Firsttime.addFirstTime(toReturn.user_ID);
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;
		}
		
		/// <summary>
		/// Edits the information of the user having the user_ID provided. If username will be changed,
		/// this function first checks if username already exists in the database, if yes, function will fail.
		/// </summary>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/> The user_ID of the user.
		/// </param>
		/// <param name="username">
		/// A <see cref="System.String"/> The new username. If no changes, pass old value.
		/// </param>
		/// <param name="firstname">
		/// A <see cref="System.String"/> The new firstname. If no changes, pass old value.
		/// </param>
		/// <param name="lastname">
		/// A <see cref="System.String"/> The new lastname. If no changes, pass old value.
		/// </param>
		/// <param name="email">
		/// A <see cref="System.String"/> The new email. If no changes, pass old value.
		/// </param>
		/// <param name="contact">
		/// A <see cref="System.String"/> The new contact number. If no changes, pass old value.
		/// </param>
		/// <param name="level">
		/// A <see cref="System.Int32"/> The new level. If no changes, pass old value.
		/// </param>
		/// <returns>
		/// A <see cref="System.Boolean"/> Returns false if new username already exists in the database.
		/// </returns>
		public static bool editUser(int user_ID, string username, string password, string firstname, string lastname, string email, string contact, int level)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT * "
					+ "FROM users WHERE username = '" + username+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
				while(reader.Read())
				{	
					if ((int)reader["user_ID"] != user_ID)
					{
						conn.Close();
						return false;	
					}
				}
				reader.Close();
				string newStatement;
				if (password.Equals("") || password.Equals(null))
					newStatement = "UPDATE users SET username='"+username+"', firstname='"+firstname+"', lastname='"+lastname
					+ "', email='"+email+ "', contactnumber='"+contact+"', level='"+level+"' WHERE user_ID='"+user_ID+"'";
				else
					newStatement = "UPDATE users SET username='"+username+"', password='"+encrypt(password)+"', firstname='"+firstname+"', lastname='"+lastname
					+ "', email='"+email+ "', contactnumber='"+contact+"', level='"+level+"' WHERE user_ID='"+user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return true;
		}
		
		/// <summary>
		/// Edits the information of the user having the user_ID provided. If username will be changed,
		/// this function first checks if username already exists in the database, if yes, function will fail.
		/// </summary>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/> The user_ID of the user.
		/// </param>
		/// <param name="username">
		/// A <see cref="System.String"/> The new username. If no changes, pass old value.
		/// </param>
		/// <param name="firstname">
		/// A <see cref="System.String"/> The new firstname. If no changes, pass old value.
		/// </param>
		/// <param name="lastname">
		/// A <see cref="System.String"/> The new lastname. If no changes, pass old value.
		/// </param>
		/// <param name="email">
		/// A <see cref="System.String"/> The new email. If no changes, pass old value.
		/// </param>
		/// <param name="contact">
		/// A <see cref="System.String"/> The new contact number. If no changes, pass old value.
		/// </param>
		/// <returns>
		/// A <see cref="System.Boolean"/> Returns false if new username already exists in the database.
		/// </returns>
		public static bool editUser(int user_ID, string username, string firstname, string lastname, string email, string contact)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT * "
					+ "FROM users WHERE username = '" + username+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
				while(reader.Read())
				{	
					if ((int)reader["user_ID"] != user_ID)
					{
						conn.Close();
						return false;	
					}
				}
				reader.Close();				
				string newStatement = "UPDATE users SET username='"+username+"', firstname='"+firstname+"', lastname='"+lastname
					+ "', email='"+email+ "', contactnumber='"+contact+"' WHERE user_ID='"+user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return true;
		}
		
		/// <summary>
		/// Changes the password of the user with the given user_ID. 
		/// </summary>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/> The user_ID of the user.
		/// </param>
		/// <param name="currentPassword">
		/// A <see cref="System.String"/> Old Password
		/// </param>
		/// <param name="newPassword">
		/// A <see cref="System.String"/> New Password
		/// </param>
		/// <returns>
		/// A <see cref="System.Boolean"/> Returns false if currentPassword does not match the old password of the user.
		/// </returns>
		public static bool changePassword(int user_ID, string currentPassword, string newPassword)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT password "
					+ "FROM users WHERE user_ID = '" + user_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        object result = cmd.ExecuteScalar();
				if (!encrypt(currentPassword).Equals((string)result))
				{
					conn.Close();
					return false;
				}
				string newStatement = "UPDATE users SET password='"+encrypt(newPassword)+"' WHERE user_ID='"+user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return true;
		}
		
		public static void changePassword(int user_ID, string newPassword)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string newStatement = "UPDATE users SET password='"+encrypt(newPassword)+"' WHERE user_ID='"+user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		public static bool changePasswordFirstLogin(int user_ID, string currentPassword, string newPassword)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT password "
					+ "FROM users WHERE user_ID = '" + user_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        object result = cmd.ExecuteScalar();
				if (!currentPassword.Equals((string)result))
				{
					conn.Close();
					return false;
				}
				string newStatement = "UPDATE users SET password='"+encrypt(newPassword)+"' WHERE user_ID='"+user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return true;
		}
		
		/// <summary>
		/// Get all the users stored in the database. 
		/// </summary>
		/// <returns>
		/// A <see cref="List<User>"/> All the users in the database in a List.
		/// </returns>
		public static List<User> getUsers()
		{
			List<User> usersList = new List<User>(0);
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT user_id "
					+ "FROM users";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					User current = new User((int) reader["user_id"]);
					usersList.Add(current);
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return usersList;
		}
		
		/// <summary>
		/// Deletes the user with the user_ID given. Also deletes corresponding entries in the loan and reservations table. 
		/// </summary>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/> The user_ID of the user to be deleted.
		/// </param>
		public static void deleteUser(int user_ID)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "DELETE FROM users where user_ID='"+user_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);
				cmd.ExecuteNonQuery();				
				string newStatement = "DELETE FROM loan where user_ID='"+user_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();				
				string newStatementAgain = "DELETE FROM loan where user_ID='"+user_ID+"'";
		        MySqlCommand newCmdAgain = new MySqlCommand(newStatementAgain, conn);
				newCmdAgain.ExecuteNonQuery();
				string statement2 = "DELETE FROM firsttime where user_ID='"+user_ID+"'";
		        MySqlCommand cmd2 = new MySqlCommand(statement2, conn);
				cmd2.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Searches among the given list of users using a search string also provided. 
		/// </summary>
		/// <param name="searchString">
		/// A <see cref="System.String"/> The string to use in the searching.
		/// </param>
		/// <param name="users">
		/// A <see cref="List<User>"/> The list of users to search from.
		/// </param>
		/// <returns>
		/// A <see cref="List<User>"/> The list of users that matched the search string.
		/// </returns>
		public static List<User> searchUsers(string searchString, List<User> users)
		{
			List<User> usersList = new List<User>(0);
			for (int i=0; i<users.Count; i++)
			{
				if (users[i].username.ToLower().Contains(searchString.ToLower()) || users[i].firstname.ToLower().Contains(searchString.ToLower()) || users[i].lastname.ToLower().Contains(searchString.ToLower()) || users[i].email.ToLower().Contains(searchString.ToLower()))
					usersList.Add(users[i]);
			}
			return usersList;
		}
		
		public static User searchUsersByUsername(string username, List<User> users)
		{
			foreach (User current in users)
			{
				if (current.username.Equals(username))
					return current;
			}
			return null;
		}
		
		/// <summary>
		/// Checks if an email follows the username@domain.com format for email. 
		/// </summary>
		/// <param name="email">
		/// A <see cref="System.String"/> The email to be checked.
		/// </param>
		/// <returns>
		/// A <see cref="System.Boolean"/> Returns true if email is valid, false otherwise.
		/// </returns>
		public static bool validateEmail(string email)
		{
			Regex matchRegex = new Regex(@"[a-zA-Z0-9_\-\.]+@.+\..+");//[a-zA-Z0-9_\-\.]+\.[a-zA-Z]{2,5}");
			MatchCollection matches  = matchRegex.Matches(email);
			if(matches.Count==0)
			 	return false;
			else
				return true;
		}
		
		private static string encrypt(string toEncrypt)
		{
			Byte[] originalBytes;
            Byte[] encodedBytes;
            MD5 md5;
            md5 = new MD5CryptoServiceProvider();
            originalBytes = ASCIIEncoding.Default.GetBytes(toEncrypt);
            encodedBytes = md5.ComputeHash(originalBytes);
            return Regex.Replace(BitConverter.ToString(encodedBytes), "-", "").ToLower();
		}
		
		public static List<Reservation> getReservations(int user_ID)
		{
			return (new User(user_ID)).getReservations();
		}
		
		public static List<Loan> getLoans(int user_ID) 
		{ 
            return (new User(user_ID)).getLoans(); 
        }
		
		public static List<Loan> getReturned(int user_ID) 
		{ 
            return (new User(user_ID)).getReturned(); 
        }
	}
}


